package com.client.db;

import com.client.bean.UserInfo;
import com.client.common.CmdControl;
import com.client.utils.JdbcUtil;
import com.mysql.jdbc.Connection;
import com.sun.deploy.panel.ITreeNode;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

/**
 * Created by Administrator on 2016/12/16.
 */
public class UserDbOper {
	//注册用户
	public boolean addUser(UserInfo userInfo) {
		boolean flag = false;
		Connection conn = null;
		PreparedStatement stat = null;

		try {

			String sql = "insert into userinfo values(?,?,?,?,?,?,?,?,?,?,?,?,?)";
			conn = (Connection) JdbcUtil.getConnection();

			stat = conn.prepareStatement(sql);	// PreparedStatement 可以缓存SQL语句，可以参数化sql语句。提高性能，防止注入

			int i = 1;
			stat.setInt(i++ , userInfo.getCode());
			stat.setString(i++ , userInfo.getNicknName());
			stat.setString(i++ , userInfo.getPassword());
			stat.setString(i++ , userInfo.getIpAddr());
			stat.setInt(i++ , userInfo.getPort());
			stat.setInt(i++ , userInfo.getAge());
			stat.setString(i++ , userInfo.getSex());
			stat.setString(i++ , userInfo.getNation());
			stat.setString(i++ , userInfo.getStar());
			stat.setString(i++ , userInfo.getFace());
			stat.setString(i++ , userInfo.getRemark());
			stat.setString(i++ , userInfo.getInformation());
			stat.setInt(i++ , userInfo.getStatus());

			if (stat.executeUpdate()>0) {
				flag = true;
			}



		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(conn);
			JdbcUtil.close(stat);
		}
		return flag;
	}

	//判断端口是否已经被占用
	public boolean isPort(int port) {
		boolean flag = false;

		Connection conn = null;
		PreparedStatement stat = null;

		try {

			String sql = "SELECT port FROM  userinfo WHERE port = ?";
			conn = (Connection) JdbcUtil.getConnection();

			stat = conn.prepareStatement(sql);	// PreparedStatement 可以缓存SQL语句，可以参数化sql语句。提高性能，防止注入

			int i = 1;

			stat.setInt(i++,port);

			ResultSet rs = stat.executeQuery();

			if (rs.next()) {
				flag = true;
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(conn);
			JdbcUtil.close(stat);
		}

		return flag;
	}

	/**
	 * 登录
	 * @param userInfo
	 * @return
	 */
	public UserInfo login(UserInfo userInfo) {

		Connection conn = null;
		PreparedStatement stat = null;

		try {

			String sql = "SELECT * FROM  userinfo WHERE code=? AND  password=?";
			conn = (Connection) JdbcUtil.getConnection();

			stat = conn.prepareStatement(sql);	// PreparedStatement 可以缓存SQL语句，可以参数化sql语句。提高性能，防止注入

			int i = 1;

			stat.setInt(i++,userInfo.getCode());
			stat.setString(i++,userInfo.getPassword());

			ResultSet rs = stat.executeQuery();

			if (rs.next()) {//登录成功
				//修改为在线状态（1）
				userInfo.setNicknName(rs.getString("nickName").trim());
				userInfo.setIpAddr(rs.getString("ipAddr").trim());
				userInfo.setPort(rs.getInt("port"));
				userInfo.setAge(rs.getInt("age"));
				userInfo.setSex(rs.getString("sex").trim());
				userInfo.setNation(rs.getString("nation").trim());
				userInfo.setStar(rs.getString("star").trim());
				userInfo.setFace(rs.getString("face").trim());
				userInfo.setRemark(rs.getString("remark").trim());
				userInfo.setInformation(rs.getString("information").trim());
				userInfo.setStatus(1);//在线
				modifyStatus(userInfo.getCode() , 1);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(conn);
			JdbcUtil.close(stat);
		}


		return userInfo;
	}

	/**
	 * 修改用户状态status=0:离线，1：在线，2：隐身，3：忙碌
	 * @param code
	 * @param status
	 * @return
	 */
	public boolean modifyStatus(int code , int status) {
		boolean flag = false;
		Connection conn = null;
		PreparedStatement stat = null;

		try {

			String sql = "UPDATE userinfo SET  status=? WHERE code=?";
			conn = (Connection) JdbcUtil.getConnection();

			stat = conn.prepareStatement(sql);	// PreparedStatement 可以缓存SQL语句，可以参数化sql语句。提高性能，防止注入

			int i = 1;
			stat.setInt(i++ , status);
			stat.setInt(i++ , code);

			if (stat.executeUpdate()>0) {
				flag = true;
			}



		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(conn);
			JdbcUtil.close(stat);
		}
		return flag;
	}

	/**
	 * 返回头像
	 * @param code
	 * @return
	 */
	public String getFace(int code) {

		String face = "";
		Connection conn = null;
		PreparedStatement stat = null;

		try {

			String sql = "SELECT face FROM  userinfo WHERE code=?";
			conn = (Connection) JdbcUtil.getConnection();

			stat = conn.prepareStatement(sql);	// PreparedStatement 可以缓存SQL语句，可以参数化sql语句。提高性能，防止注入

			int i = 1;

			stat.setInt(i++,code);


			ResultSet rs = stat.executeQuery();

			if (rs.next()) {//登录成功
				//修改为在线状态（1）
				face = rs.getString("face").trim();

			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(conn);
			JdbcUtil.close(stat);
		}


		return face;
	}

	/**
	 * 返回所有好友，家人，同学，黑名单等资料
	 * @param userInfo
	 * @return
	 */
	public Vector<UserInfo> getAllInfo(UserInfo userInfo) {

		Vector<UserInfo> allInfo = new Vector<UserInfo>();
		Connection conn = null;
		PreparedStatement stat = null;

		try {
//			String sql = "SELECT * FROM userinfo WHERE code IN (SELECT friendAccount FROM friendinfo WHERE selfAccount = ?)";
			String sql = "SELECT a.* , f.groupname FROM userinfo a RIGHT OUTER JOIN friendinfo f on a.code=f.friendAccount WHERE f.selfAccount = ?";
			conn = (Connection) JdbcUtil.getConnection();

			stat = conn.prepareStatement(sql);	// PreparedStatement 可以缓存SQL语句，可以参数化sql语句。提高性能，防止注入

			int i = 1;

			stat.setInt(i++,userInfo.getCode());


			ResultSet rs = stat.executeQuery();

			while (rs.next()) {
				UserInfo u = new UserInfo();
				u.setCode(rs.getInt("code"));
				u.setPassword(rs.getString("password"));
				u.setNicknName(rs.getString("nickName").trim());
				u.setIpAddr(rs.getString("ipAddr").trim());
				u.setPort(rs.getInt("port"));
				u.setAge(rs.getInt("age"));
				u.setSex(rs.getString("sex").trim());
				u.setNation(rs.getString("nation").trim());
				u.setStar(rs.getString("star").trim());
				u.setFace(rs.getString("face").trim());
				u.setRemark(rs.getString("remark").trim());
				u.setInformation(rs.getString("information").trim());
				u.setStatus(rs.getInt("status"));
				u.setGroupname(rs.getString("groupname").trim());
				allInfo.add(u);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(conn);
			JdbcUtil.close(stat);
		}


		return allInfo;
	}

	/**
	 * 查找好友
	 * @param userInfo
	 * @return
	 */
	public Vector<UserInfo> find(UserInfo userInfo) {
		Connection conn = null;
		Statement stat = null;
		Vector allInfo = new Vector();

		try {

			String sql = "SELECT * FROM  userinfo WHERE sex = '" + userInfo.getSex() + "'";
			conn = (Connection) JdbcUtil.getConnection();

			if (userInfo.getCode() != 0) {
				sql += "and code = " +userInfo.getCode();
			}
			if (userInfo.getNicknName() != null && !userInfo.getNicknName().equals("")) {
				sql += "and nickname LIKE '%' " + userInfo.getNicknName() + "%'";
			}
			if (userInfo.getAge() != 0){
				sql += "and age=" + userInfo.getAge();
			}

			stat = conn.createStatement();	// PreparedStatement 可以缓存SQL语句，可以参数化sql语句。提高性能，防止注入

			ResultSet rs = stat.executeQuery(sql);

			while (rs.next()) {
				Vector rowData = new Vector();
				rowData.addElement(rs.getInt("code"));
				rowData.addElement(rs.getString("nickname").trim());
//				rowData.addElement(rs.getString("password"));
//				rowData.addElement(rs.getString("ipAddr").trim());
//				rowData.addElement(rs.getInt("port"));
				rowData.addElement(rs.getInt("age"));
				rowData.addElement(rs.getString("sex").trim());
				rowData.addElement(rs.getString("nation").trim());
				rowData.addElement(rs.getString("star").trim());
				rowData.addElement(rs.getString("face").trim());
				rowData.addElement(rs.getString("remark").trim());
//				rowData.addElement(rs.getString("information").trim());
				rowData.addElement(rs.getInt("status"));
//				rowData.addElement(rs.getString("groupname").trim());
				allInfo.add(rowData);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(conn);
			JdbcUtil.close(stat);
		}


		return allInfo;
	}

	/**
	 * 查找个人资料
	 * @param code
	 * @return
	 */
	public UserInfo findByCode(int code) {

		Connection conn = null;
		PreparedStatement stat = null;
		UserInfo userInfo = new UserInfo();

		try {

			String sql = "SELECT * FROM  userinfo WHERE code=?";
			conn = (Connection) JdbcUtil.getConnection();

			stat = conn.prepareStatement(sql);	// PreparedStatement 可以缓存SQL语句，可以参数化sql语句。提高性能，防止注入

			int i = 1;

			stat.setInt(i++ , code);

			ResultSet rs = stat.executeQuery();

			if (rs.next()) {
				userInfo.setCode(rs.getInt("code"));
				userInfo.setNicknName(rs.getString("nickName").trim());
				userInfo.setPassword(rs.getString("password").trim());
				userInfo.setIpAddr(rs.getString("ipAddr").trim());
				userInfo.setPort(rs.getInt("port"));
				userInfo.setAge(rs.getInt("age"));
				userInfo.setSex(rs.getString("sex").trim());
				userInfo.setNation(rs.getString("nation").trim());
				userInfo.setStar(rs.getString("star").trim());
				userInfo.setFace(rs.getString("face").trim());
				userInfo.setRemark(rs.getString("remark").trim());
				userInfo.setInformation(rs.getString("information").trim());
				userInfo.setStatus(rs.getInt("status"));
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(conn);
			JdbcUtil.close(stat);
		}


		return userInfo;
	}

	/**
	 * 添加好友
	 * @param userInfo
	 * @return
	 */
	public boolean addFriend(UserInfo userInfo , int friendcode) {
		Connection conn = null;
		PreparedStatement stat = null;

		try {

			String sql = "insert into friendinfo(selfAccount , friendAccount ,groupname , invalid) values(?,?,?,?)";
			conn = (Connection) JdbcUtil.getConnection();

			stat = conn.prepareStatement(sql);	// PreparedStatement 可以缓存SQL语句，可以参数化sql语句。提高性能，防止注入

			int i = 1;
			stat.setInt(i++ , userInfo.getCode());
			stat.setInt(i++ , friendcode);
			stat.setString(i++ , CmdControl.F_FRIEND);
			stat.setInt(i++ , 0);
			stat.executeUpdate();
			JdbcUtil.close(stat);


			stat = conn.prepareStatement(sql);
			i = 1;
			stat.setInt(i++ , friendcode);
			stat.setInt(i++ , userInfo.getCode());
			stat.setString(i++ , CmdControl.F_FRIEND);
			stat.setInt(i++ , 0);
			stat.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(conn);
			JdbcUtil.close(stat);
		}
		return true;
	}

	/**
	 * 删除好友
	 * @param userInfo
	 * @param friendcode
	 * @return
	 */
	public boolean delFriend(UserInfo userInfo , int friendcode) {
		Connection conn = null;
		PreparedStatement stat = null;

		try {

			String sql = "DELETE FROM friendinfo WHERE selfAccount = ? AND friendAccount = ?";
			conn = (Connection) JdbcUtil.getConnection();

			stat = conn.prepareStatement(sql);	// PreparedStatement 可以缓存SQL语句，可以参数化sql语句。提高性能，防止注入

			int i = 1;
			stat.setInt(i++ , userInfo.getCode());
			stat.setInt(i++ , friendcode);

			stat.executeUpdate();
			JdbcUtil.close(stat);


			stat = conn.prepareStatement(sql);
			i = 1;
			stat.setInt(i++ , friendcode);
			stat.setInt(i++ , userInfo.getCode());

			stat.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(conn);
			JdbcUtil.close(stat);
		}
		return true;
	}

	/**
	 * 修改好友分组
	 * @param userInfo
	 * @param friendcode
	 * @return
	 */
	public boolean movFriend(UserInfo userInfo , int friendcode ,  String groupname) {
		Connection conn = null;
		PreparedStatement stat = null;

		try {

			String sql = "UPDATE friendinfo SET groupname=? WHERE selfAccount = ? AND friendAccount = ?";
			conn = (Connection) JdbcUtil.getConnection();

			stat = conn.prepareStatement(sql);	// PreparedStatement 可以缓存SQL语句，可以参数化sql语句。提高性能，防止注入

			int i = 1;
			stat.setString(i++ , groupname);
			stat.setInt(i++ , userInfo.getCode());
			stat.setInt(i++ , friendcode);
			stat.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(conn);
			JdbcUtil.close(stat);
		}
		return true;
	}

	/**
	 * 更新用户资料
	 * @param userInfo
	 * @return
	 */
	public boolean updateUserInfo(UserInfo userInfo) {
		Connection conn = null;
		PreparedStatement stat = null;

		try {

			String sql = "UPDATE userinfo SET nickname=?,age=?,sex=?,nation=?,star=?,face=?,remark=?,information=? WHERE code=?";
			conn = (Connection) JdbcUtil.getConnection();

			stat = conn.prepareStatement(sql);	// PreparedStatement 可以缓存SQL语句，可以参数化sql语句。提高性能，防止注入

			int i = 1;
			stat.setString(i++ , userInfo.getNicknName());
			stat.setInt(i++ , userInfo.getAge());
			stat.setString(i++ , userInfo.getSex());
			stat.setString(i++ , userInfo.getNation());
			stat.setString(i++ , userInfo.getStar());
			stat.setString(i++ , userInfo.getFace());
			stat.setString(i++ , userInfo.getRemark());
			stat.setString(i++ , userInfo.getInformation());
			stat.setInt(i++ , userInfo.getCode());
			stat.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(conn);
			JdbcUtil.close(stat);
		}
		return true;
	}

}
